using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 车主和特殊车辆查询工具
/// 覆盖场景：V-006 车主信息, V-007 电动车记录, V-008 白名单车辆
/// </summary>
public class GetVehicleSpecialInfoTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetVehicleSpecialInfoTool> _logger;

    public string Name => "get_vehicle_special_info";
    public string Description => "查询车主信息、电动车记录、白名单车辆等特殊车辆信息。用于回答'查询车主信息'、'电动车违规记录'、'白名单车辆有哪些'等问题。";

    public GetVehicleSpecialInfoTool(
        IDatabaseExecutor executor,
        ILogger<GetVehicleSpecialInfoTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            string queryType = parameters.ContainsKey("queryType") ? parameters["queryType"]?.ToString() ?? "owner" : "owner";
            string? ownerName = parameters.ContainsKey("ownerName") ? parameters["ownerName"]?.ToString() : null;
            string? queryDate = parameters.ContainsKey("queryDate") ? parameters["queryDate"]?.ToString() : null;

            if (string.IsNullOrEmpty(queryDate))
            {
                queryDate = DateTime.Today.ToString("yyyy-MM-dd");
            }

            var sql = queryType switch
            {
                "electric" => BuildElectricVehicleQuery(queryDate),
                "whitelist" => BuildWhitelistQuery(),
                _ => BuildOwnerInfoQuery(ownerName)
            };

            _logger.LogInformation("Executing vehicle special info query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "车辆特殊信息查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["queryType"] = queryType
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing vehicle special info query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建车主信息查询
    /// </summary>
    private string BuildOwnerInfoQuery(string? ownerName)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    co.Name AS owner_name,");
        sb.AppendLine("    co.PhoneNumber AS phone_number,");
        sb.AppendLine("    m.PlateNumber AS plate_number,");
        sb.AppendLine("    m.VehicleType AS vehicle_type,");
        sb.AppendLine("    CASE");
        sb.AppendLine("        WHEN m.OutTime IS NULL THEN '在校'");
        sb.AppendLine("        ELSE '已离校'");
        sb.AppendLine("    END AS status");
        sb.AppendLine("FROM TblCarOwner co");
        sb.AppendLine("LEFT JOIN TblMotorVehicleManagement m ON co.ID = m.OwnerId");
        sb.AppendLine("WHERE 1=1");

        if (!string.IsNullOrEmpty(ownerName))
        {
            sb.AppendLine($"    AND co.Name LIKE '%{ownerName}%'");
        }

        sb.AppendLine("LIMIT 50");

        return sb.ToString();
    }

    /// <summary>
    /// 构建电动车记录查询
    /// </summary>
    private string BuildElectricVehicleQuery(string queryDate)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    DATE_FORMAT(EntranceTime, '%Y-%m-%d %H:%i:%s') AS entrance_time,");
        sb.AppendLine("    EntranceLocation AS entrance_location");
        sb.AppendLine("FROM TblElectricVehicleManagement");
        sb.AppendLine($"WHERE DATE(EntranceTime) = '{queryDate}'");
        sb.AppendLine("ORDER BY EntranceTime DESC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }

    /// <summary>
    /// 构建白名单车辆查询
    /// </summary>
    private string BuildWhitelistQuery()
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    PlateNumber AS plate_number,");
        sb.AppendLine("    Owner AS owner,");
        sb.AppendLine("    Reason AS reason,");
        sb.AppendLine("    CASE IsValid");
        sb.AppendLine("        WHEN 1 THEN '有效'");
        sb.AppendLine("        ELSE '无效'");
        sb.AppendLine("    END AS validity_status");
        sb.AppendLine("FROM TblWhiteList");
        sb.AppendLine("WHERE IsValid = 1");
        sb.AppendLine("ORDER BY PlateNumber");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }
}
